﻿


CREATE PROCEDURE [usix].[s_Comments_Update]
	@IsSync bit = 0,
	@ReLoad bit = 0
AS

IF @IsSync = 0 BEGIN
	EXEC sp__sched_Job 'Run s_Comments_Update','EXEC usix.s_Comments_Update 1'
	RETURN
END

DECLARE @Today datetime SET @Today = dbo.Today(GETDATE())- 20

BEGIN TRAN --[1

DELETE usix.t_Comment WHERE Date >= @Today

INSERT INTO usix.t_Comment([Id #], Date, Remark, [Due Date], CompanyID,Agent, ID)
SELECT [Id #], Date,
	ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'(.+)[\[\(][^\[\(]+[\]\)]$',1),''),Remark),
	[Due Date],	CompanyID,
	ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'[\[\(]([^\[\(]+)[\]\)]$',1),''),'USIX'),
	ID
FROM DataMining.dbo.ALL_Comments WITH(nolock)
WHERE Date >= @Today

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[usix].[t_Comment_Last]') AND name = N'PK_t_Comment_Last')
ALTER TABLE [usix].[t_Comment_Last] ADD  CONSTRAINT [PK_t_Comment_Last] PRIMARY KEY CLUSTERED (	[Id #] ASC,	[CompanyID] ASC)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
TRUNCATE TABLE usix.t_Comment_Last
INSERT INTO usix.t_Comment_Last
SELECT *
FROM usix.v_Comment_Last

IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END

IF @ReLoad = 1 BEGIN
	SELECT IDENTITY(int,1,1) ID, [Id #], Date,
	ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'(.+)[\[\(][^\[\(]+[\]\)]$',1),''),Remark) Remark,
	[Due Date], CompanyID,
	ISNULL(NULLIF(dbo.clr_RegEx_MatchGroup(Remark,'[\[\(]([^\[\(]+)[\]\)]$',1),''),'USIX') Agent
	INTO usix.t_Comment#Temp
	FROM DataMining.dbo.ALL_Comments WITH(nolock)
	
	IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END

	CREATE CLUSTERED INDEX [IX_t_Comment_Id] ON [usix].[t_Comment#Temp] ([Id #] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IX_t_Comment_Date] ON [usix].[t_Comment#Temp] (	[Date] DESC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usix].[t_Comment]') AND type in (N'U'))
	DROP TABLE usix.t_Comment 

	EXEC sp_rename 'usix.t_Comment#Temp','t_Comment' 
END

COMMIT --1]






